-- 初始数据库架构
-- 创建用户表和相关的枚举类型

-- 创建用户状态枚举类型
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'pending', 'disabled');

-- 创建用户角色枚举类型
CREATE TYPE user_role AS ENUM ('user', 'admin', 'super_admin');

-- 创建用户表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    avatar_url TEXT,
    status user_status NOT NULL DEFAULT 'active',
    role user_role NOT NULL DEFAULT 'user',
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    last_login_at TIMESTAMP WITH TIME ZONE
);

-- 创建索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_created_at ON users(created_at);

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 创建更新时间触发器
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 插入默认管理员用户（密码：admin123）
INSERT INTO users (username, email, password_hash, full_name, role, status)
VALUES (
    'admin',
    'admin@example.com',
    '$argon2id$v=19$m=65536,t=3,p=4$dGVzdA$dGVzdA', -- 这里应该是实际的哈希值
    'Administrator',
    'admin',
    'active'
);

-- 创建会话表（用于JWT令牌黑名单等）
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    last_activity_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

-- 创建会话索引
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_token_hash ON sessions(token_hash);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
CREATE INDEX idx_sessions_is_active ON sessions(is_active);

-- 创建用户活动日志表
CREATE TABLE user_activity_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    action VARCHAR(100) NOT NULL,
    details JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- 创建活动日志索引
CREATE INDEX idx_user_activity_logs_user_id ON user_activity_logs(user_id);
CREATE INDEX idx_user_activity_logs_action ON user_activity_logs(action);
CREATE INDEX idx_user_activity_logs_created_at ON user_activity_logs(created_at);

-- 创建WebSocket房间表
CREATE TABLE websocket_rooms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    is_public BOOLEAN NOT NULL DEFAULT TRUE,
    max_users INTEGER DEFAULT 100,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- 创建房间索引
CREATE INDEX idx_websocket_rooms_name ON websocket_rooms(name);
CREATE INDEX idx_websocket_rooms_created_by ON websocket_rooms(created_by);
CREATE INDEX idx_websocket_rooms_is_public ON websocket_rooms(is_public);

-- 创建房间更新时间触发器
CREATE TRIGGER update_websocket_rooms_updated_at
    BEFORE UPDATE ON websocket_rooms
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 创建WebSocket消息表
CREATE TABLE websocket_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id UUID NOT NULL REFERENCES websocket_rooms(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    message_type VARCHAR(50) NOT NULL DEFAULT 'chat',
    content TEXT NOT NULL,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- 创建消息索引
CREATE INDEX idx_websocket_messages_room_id ON websocket_messages(room_id);
CREATE INDEX idx_websocket_messages_user_id ON websocket_messages(user_id);
CREATE INDEX idx_websocket_messages_message_type ON websocket_messages(message_type);
CREATE INDEX idx_websocket_messages_created_at ON websocket_messages(created_at);

-- 创建系统配置表
CREATE TABLE system_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key VARCHAR(100) UNIQUE NOT NULL,
    value TEXT NOT NULL,
    description TEXT,
    is_encrypted BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- 创建配置索引
CREATE INDEX idx_system_configs_key ON system_configs(key);

-- 创建配置更新时间触发器
CREATE TRIGGER update_system_configs_updated_at
    BEFORE UPDATE ON system_configs
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 插入默认系统配置
INSERT INTO system_configs (key, value, description) VALUES
('app_name', 'Rust Web Service Demo', '应用程序名称'),
('app_version', '1.0.0', '应用程序版本'),
('maintenance_mode', 'false', '维护模式开关'),
('max_login_attempts', '5', '最大登录尝试次数'),
('session_timeout_minutes', '60', '会话超时时间（分钟）'),
('password_min_length', '8', '密码最小长度'),
('enable_registration', 'true', '是否允许用户注册'),
('require_email_verification', 'false', '是否需要邮箱验证');

-- 创建数据库统计视图
CREATE VIEW user_statistics AS
SELECT
    COUNT(*) as total_users,
    COUNT(*) FILTER (WHERE status = 'active') as active_users,
    COUNT(*) FILTER (WHERE status = 'inactive') as inactive_users,
    COUNT(*) FILTER (WHERE status = 'pending') as pending_users,
    COUNT(*) FILTER (WHERE status = 'disabled') as disabled_users,
    COUNT(*) FILTER (WHERE role = 'user') as regular_users,
    COUNT(*) FILTER (WHERE role = 'admin') as admin_users,
    COUNT(*) FILTER (WHERE role = 'super_admin') as super_admin_users,
    COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '24 hours') as new_users_24h,
    COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '7 days') as new_users_7d,
    COUNT(*) FILTER (WHERE last_login_at >= NOW() - INTERVAL '24 hours') as active_users_24h
FROM users;

-- 创建房间统计视图
CREATE VIEW room_statistics AS
SELECT
    r.id,
    r.name,
    r.description,
    r.is_public,
    r.max_users,
    COUNT(m.id) as message_count,
    COUNT(DISTINCT m.user_id) as unique_users,
    r.created_at,
    r.updated_at
FROM websocket_rooms r
LEFT JOIN websocket_messages m ON r.id = m.room_id
GROUP BY r.id, r.name, r.description, r.is_public, r.max_users, r.created_at, r.updated_at;

-- 添加注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON TABLE sessions IS '用户会话表';
COMMENT ON TABLE user_activity_logs IS '用户活动日志表';
COMMENT ON TABLE websocket_rooms IS 'WebSocket房间表';
COMMENT ON TABLE websocket_messages IS 'WebSocket消息表';
COMMENT ON TABLE system_configs IS '系统配置表';

COMMENT ON COLUMN users.password_hash IS '密码哈希值';
COMMENT ON COLUMN users.status IS '用户状态';
COMMENT ON COLUMN users.role IS '用户角色';
COMMENT ON COLUMN sessions.token_hash IS '令牌哈希值';
COMMENT ON COLUMN user_activity_logs.details IS '活动详情JSON';
COMMENT ON COLUMN websocket_messages.metadata IS '消息元数据JSON';
COMMENT ON COLUMN system_configs.is_encrypted IS '配置值是否加密';
